import MySQLdb
from lib import globalVar as GV
import os
import time
from datetime import date

class papyDB(object):    

    def __init__( self ):
        self.dbConn()
        self.assetType = self.getEnumItem('ASSET','assetType')
        
#    def __del__(self):
#        self.dbclose()
    
    def dbConn( self ):
        self.db = MySQLdb.connect(host=GV.DATABASE_HOST , user='root' , passwd='studio77' , db=GV.MAIN_DATABASE , charset='utf8')
        self.cr = self.db.cursor()
    
    def dbclose( self ):
        if self.db.open:
            self.db.close()
        
    def query( self , query ):
        self.dbConn()
        self.cr.execute(query)
        self.db.commit()
        self.dbclose()    
    
    def getFetch( self , query ):
        self.dbConn()        
        self.cr.execute( query )
        data = self.cr.fetchall()
        self.dbclose()               
        return data
    
    def userID( self ):
        self.dbConn()
        query = 'select userID from USER_INFO where userLogInname ="%s"' % os.environ['username']
        self.dbclose()
        return int(self.getFetch( query )[0][0])
    
    def getID( self , table , ID , where , value ):
        self.dbConn()
        theDic = { 'table':table , 'ID':ID , 'where':where , 'value':value }
        query = 'select %(ID)s from %(table)s where %(where)s = "%(value)s"' % theDic
#        print query
        theResult = int( self.getFetch(query)[0][0] )
        self.dbclose()
        return theResult
        
    def getAttr( self , table ):
        self.dbConn()
        temp = []
        for x in self.getFetch( 'desc %s' % table ):
            if not 'auto_increment' in x:                
                temp.append( x[0] )   
        self.dbclose()             
        return temp        
       
    def getEnumItem( self , table , attr ):
        self.dbConn()
        items = [ x for x in self.getFetch('desc ' +table ) if attr in x[0] ][0][1]
        items = items.replace('enum(','')
        items = eval( items.replace(')','') )     
        self.dbclose()   
        return items
        
    def regTable( self , table , *arg ):
        self.dbConn()
        attr = self.getAttr(table)      
        setAttr = len(arg)*'%s,'
        setValues = len(arg)*'"%s",'
        query = 'insert into %s ('+setAttr[:-1]+') values('+setValues[:-1]+')' 
        format = tuple( [table]+attr + list(arg) )
#        print query % format
        self.query( query % format )        
        
    def getLastID( self , table , ID ):
        self.dbConn()
        query = 'select %(ID)s from %(table)s order by %(ID)s desc limit 1' % {'table':table , 'ID':ID}
        if self.getFetch(query) == () :
            result = 0
        else :
            result = self.getFetch(query)[0][0]        
        return int(result)     
            
    def test( self , table , *arg ):
        self.dbConn()
        attr = self.getAttr(table)
        setAttr = len(arg)*'%s,'
        setValues = len(arg)*'"%s",'
        txt = ''
        for i,x in enumerate(attr):
            txt += x+'='+ str(arg[i]) + ','
        returnQuery = 'select * from %(table)s where %(txt)s' % {'table':table , 'txt':txt}
        result = self.getFetch(returnQuery)        
        
    def closeEvent( self , event ) :
        self.dbclose()        
        
    def refineDate( self , inputDate ):
        inputDate = inputDate.split('-')
        for x in range(2) :
            if len(inputDate[x+1]) == 1 :
                inputDate[x+1] =  inputDate[x+1].zfill(2)         
        inputDate = inputDate[0] + '-' + inputDate[1] + '-' + inputDate[2]   
#        print inputDate
        return str(inputDate)
    
    def getTodayDate( self ):
        today = str(date.today())
        return today
        











    
class tableInfo(papyDB):
    
    def __init__( self , table , prjName='' , prjCodeName='' ): 
        super(tableInfo,self).__init__()      
        self.table = table
        self.prjName = prjName
        self.prjCodeName = prjCodeName              
        self.prjID = self.getprjID()
    
#    def __del__(self):
#        self.dbclose()
        
    def search( self , col, **options ):
        if 'repetition' in options.keys():
            repetition = ' distinct '
        else : 
            repetition = ''
        self.dbConn()
        
        txt1 = 'select ' 
        txt2 = ' from %s ' % self.table
        query = txt1 + repetition + col + txt2  
        if type([]) in [ type(x) for x in options.values()] or None:
            return          
        if options != {} and not '' in options.values():
            query += ' where '
            for key in options :
                query += key +"=\'" + str(options[key]) + "\' AND "
            query = query[:-4]        
#        print query                                               
        result = []
        if self.getFetch(query) == []:            
            return 0
        self.dbConn()
        for x in self.getFetch(query):
            if type(x[0]) == type(1L):
                result.append( int(x[0]) )
            else :
                result.append( x[0] )                  
        if len( result ) == 1 and not 'repetition' in options.keys():  
            return result[0]                                   
        else :                  
            return result
   
    def searches( self , *col, **options ):
        
        if 'repetition' in options.keys():
            repetition = ' distinct '
        else : 
            repetition = ''
        self.dbConn()
        selCol = ''
        for x in col:
            selCol += x + ' ,'
        selCol = selCol[:-1]        
        txt1 = 'select ' 
        txt2 = ' from %s ' % self.table
        query = txt1 + repetition + selCol + txt2  
        if type([]) in [ type(x) for x in options.values()] or None:
            return          
        if options != {} and not '' in options.values():
            query += ' where '
            for key in options:
                query += key +"=\'" + str(options[key]) + "\' AND "
            query = query[:-4]        
#        print query                                               
        result = []
        if self.getFetch(query) == []:            
            return 0
        self.dbConn()
        for x in self.getFetch(query):
            if type(x[0]) == type(1L):
                result.append( int(x[0]) )
            else :
                result.append( x )                  
        if len( result ) == 1 and not 'repetition' in options.keys():           
            return result[0]                                   
        else :                    
            return result      
        
#    def register(self , *arg ):
#        self.dbConn()
#        attr = self.getAttr(self.table)      
#        setAttr = len(arg)*'%s,'
#        setValues = len(arg)*'"%s",'
#        query = 'insert into %s ('+setAttr[:-1]+') values('+setValues[:-1]+')'           
#        format = tuple( [self.table]+attr + list(arg) )
#        query = query % format        
#        self.query( query ) 
    
    def register(self , **key ):
        self.dbConn()
        attr = self.getAttr(self.table) 
        query = "insert into %s (" % self.table
        for x in key:
            query += x +"," 
        query = query[:-1] + ") values("
        for x in key:
            query += "'"+key[x] +"',"
        query = query[:-1] + ')'
        self.query( query ) 
#        print query
               
    def deleteRow(self , **key ):
        self.dbConn()
        query = "delete from  %s where " % self.table
        for x in key:
            query += x + " = '" + str( key[x] ) + "' AND "
        query = query[:-4]
#        print query
        self.query( query )
        
            
    def update(self , col , value , **key): 
        self.dbConn()  
        value = value.replace('\'' , '"')
        query = "UPDATE %(table)s SET %(col)s= '%(value)s' WHERE " % {'table':self.table , 'col':col , 'value':value}
        for x in key:
            query += x +"='" + str(key[x]) + "' AND "
        query = query[:-4]
#        print query
        self.query( query )        

            
    def getprjID(self):  
        self.dbConn()      
        prjID1 = self.getFetch('select prjID from PROJECT where  prjName="%s"' % self.prjName)
        prjID2 = self.getFetch('select prjID from PROJECT where  prjCodeName="%s"' % self.prjCodeName)
        if  prjID1 != () :            
            return prjID1[0][0]
        elif prjID2 != ():            
            return prjID2[0][0]
        else :            
            return 
        
        



     
if __name__ == '__main__':    
    papyDB = papyDB()
    
    
    
    